alter PROC sp_create_update_sort_lot_data_entry 
(
   @part_number varchar(50), 
   @lot_number varchar(50), 
   @lot_status varchar(50), 
   @wafer_qty_in int, 
   @Po_No varchar(50),
   @date_in varchar(50),
   @lot_stage_name varchar(50),
   @expected_date_out varchar (50),
   @vendor_name varchar(50),
   @quantity_out varchar(50), 
   @date_out varchar(50),
   @sort_type varchar(50),
   @gross_Die varchar(50),
   @ship_Yield varchar(50),
   @die_size float,
   @avg_good_die float,
   @date_code varchar(50),
   @vendor_lot varchar(50),
   @lot_comments varchar(1000),
   @lot_category varchar(50)
)
  as
  begin
  	declare @processStepId int
   	declare @locationId int
   	declare @lot_id int
   	declare @this_lot_stauts varchar(50)
   	   
   	declare @date_time datetime
   	declare @start_date datetime
   	declare @end_date datetime
 	  	  	
   	select @processStepId = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name = 'SORT' and PS_Type = @sort_type and product.ProcessStep_ID = Process_Step.ProcessStep_ID 
	select @locationId = Location_ID from Location where L_Type = 'Sort' and L_Vendor_Name = @vendor_name
   	if @processStepId is null -- new Product or Product Name mismatch
   	begin	
		SELECT @processStepId = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'SORT' AND PS_TYPE = @sort_type AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @locationId
  		IF @processStepId IS NULL 
  		BEGIN
   			-- dummy update to avoid SQLException
   			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
   			return
		END
   	end
   	
   	select @this_lot_stauts = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @processStepId and Location_ID = @locationId and Lot_Number = @lot_number

   	if @lot_id is null -- Check if the lot exists
   	begin -- Create Lot
   		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Date_In, Date_Out,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Comments, PO_Number, Lot_Date_Code) 
 			values (@processStepId, @locationId, @lot_number, @lot_number, @vendor_lot, @wafer_qty_in, @date_in,@date_out, @lot_status, CURRENT_TIMESTAMP, 0, @lot_category, @lot_Comments, @PO_No, @date_code )
 		select @lot_id = @@IDENTITY
 		
		-- Insert Sort Lot
 		insert into Sort_Lot(Lot_ID, SL_Gross_Die, Timestamp ) values (@lot_id, @gross_Die, CURRENT_TIMESTAMP )
   	end
   	else 
   	begin
   		declare @temp_varchar varchar(20)
 		set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
 		set @start_date = convert(datetime, @temp_varchar)
 		set @temp_varchar = null
 		set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
 		set @end_date = convert(datetime, @temp_varchar)	
 		
 		/* Updating into lot History Table
 		   Started Here*/
 		select @date_time = (Lot_Table_History.timestamp) from Lot_Table_History where Lot_ID = @lot_id
 		
		if @date_time between @start_date and @end_date
		begin
			update Lot_Table_History set Lot_Current_Status = @LOT_STATUS, Wafer_Qty_In = @wafer_qty_in, Good_Die_Qty = @quantity_out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Category = @lot_category, PO_Number = @Po_No where Lot_ID = @lot_id
		end
		else
		begin	
			insert into Lot_Table_History(Lot_ID, Processstep_Id, Location_Id,Lot_Number,Fab_Lot_Number,Vendor_Lot_Number,Lot_Entered_By,Lot_Manual_Hold,Lot_Category,Wafer_Qty_In,Wafer_Qty_Out,Qty_In,Qty_Out,Date_In,Date_Out,Stage_Name,Stage_Position,Lot_Current_Status,Lot_Comments,Timestamp, PO_Number, Shop_Order_Number, Lot_Date_Code)
				(select Lot_ID, Processstep_Id, Location_Id, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number,Lot_Entered_By,Lot_Manual_Hold,Lot_Category,Wafer_Qty_In,Wafer_Qty_Out,Qty_In,Qty_Out,Date_In,Date_Out,Stage_Name,Stage_Position,Lot_Current_Status,Lot_Comments,CURRENT_TIMESTAMP, Po_Number, Shop_Order_Number, Lot_Date_Code from Lot_Table where Lot_id = @lot_id)
		end
 		/*Ended Here*/
 		
 		update Lot_Table set Lot_Current_Status = @LOT_STATUS, Wafer_Qty_In = @wafer_qty_in, Good_Die_Qty = @quantity_out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments,Lot_Category = @lot_category, PO_Number = @PO_NO, Lot_Date_Code = @date_code where Lot_ID = @lot_id
 		
 		update Sort_Lot set Timestamp = CURRENT_TIMESTAMP, SL_Gross_Die =  @gross_Die   where Lot_ID = @lot_id
	END
	
	--Inserting into History table when lot is having dateout and Quantity Out		
 	if @quantity_out is not null and @DATE_OUT is not null
 	begin
 		-- select * from Lot_table
 		update Lot_Table set Lot_Current_Status = 'Close', Wafer_Qty_In = @wafer_qty_in, Good_Die_Qty = @quantity_out, Date_In = @date_in, Date_Out = @Date_Out, Lot_Comments = @Lot_Comments, Lot_Category = @lot_category, PO_Number = @Po_No, Lot_Date_Code = @date_code  where Lot_ID = @lot_id
 	
 		insert into Lot_Table_History (Lot_Id, ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Date_In, Date_Out,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, PO_Number, Lot_Date_Code) 
 		values ( @lot_id, @processStepId, @locationId, @lot_number, @lot_number, @vendor_lot, @wafer_qty_in, @date_in,@date_out, 'Close', CURRENT_TIMESTAMP, 0, @lot_category, @PO_No, @date_code)		
 	
 	end	
 	/* Closing Transit Lots and Previous Process Lots*/
 	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @lot_number,@WAFER_QTY_IN, @date_in    				

   		/*To close the lot, if lot in Transit*/
         	--EXEC SP_CLOSE_TRANSIT_LOTS_BY_SHIPPNG_LOT_ID @process_step_id, @LOT_NUMBER, @LOCATION_ID, @quantity, @DATE_IN
 		/* To close the lot, if lot in running state in previous location */
 	 	--EXEC SP_CLOSE_LOTS_BY_PREVIOUS_PROCESS_STEP_ID @process_step_id, @LOT_NUMBER, @LOCATION_ID, 'SORT'
   end
  
  

